/*
-------------------------------------------------------------------
BIE is Copyright 2001-2004 Brunswick Corp.
-------------------------------------------------------------------
Please read the legal notices (docs/legal.txt) and the license
(docs/bie_license.txt) that came with this distribution before using
this software.
-------------------------------------------------------------------

*/
/*
 * parseCertFile.java
 *
 * Created on May 6, 2003, 10:50 AM
 */

package tests.jd;

import java.io.*;
import java.util.*;

/**
 *
 * @author  jd
 */
public class parseCertFile {
    
    private static ArrayList columns = new ArrayList();
    private static ArrayList types = new ArrayList();
                                                   
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws Exception{                
        String descFile = "c:/biedev/apsv.txt"; 
        String dataFile = "c:/biedev/apsvdata.txt";
        
        System.out.println("loading list...");
        loadLists(descFile);
        System.out.println("creating tables...");        
        System.out.println(createTableStmt());   
        System.out.println("creating inserts...");
        System.out.println(createInserts(dataFile));    
    }
    
    private static void loadLists (String descFile) throws Exception{
        FileInputStream fis = new FileInputStream(new File(descFile));
        BufferedReader br = new BufferedReader(new InputStreamReader(fis));
        
        String line = "";
        
        //skip to the data
        for (int i=0;i<21;i++) {
            br.readLine();
        }
        
        //Order Field Name                Data Type   Flags Format          Initial
        //   10 cono                      inte        i     >>>9            0

        while(((line = br.readLine()) != null) && line.length() > 3) {
            //String order = line.substring(0,5).trim();
            String field = line.substring(6,31).trim();
            String dataType = line.substring(32,43).trim();
            //String flag = line.substring(44,49).trim();
            //String format = line.substring(50,line.length()-1).trim();
            columns.add(field);
            
            if (dataType.indexOf("inte") != -1) {
                types.add("INT");
            }else if (dataType.indexOf("deci") != -1) {
                types.add("FLOAT");
            }else if (dataType.indexOf("char") != -1) {
                types.add("VARCHAR(255)");
            }else if (dataType.indexOf("logi") != -1) {
                types.add("LOGI");
            }else if (dataType.indexOf("date") != -1) {
                types.add("DATE");
            }
            //System.out.print(order + "!!" + field + "!!"+ dataType + "!!"+ flag + "!!"+ format + "\n" );
        }
        
        br.close();
        fis.close();                       
    }

    private static String createTableStmt() {                
        String cs = "CREATE TABLE APSV(";
        Iterator cIt = columns.iterator();
        Iterator tIt = types.iterator();
        while (cIt.hasNext()) {
            String col = (String) cIt.next();
            String type = (String) tIt.next();
            
            if (type.indexOf("LOGI") != -1) {
                type = "VARCHAR(255)";
            }
            if (type.indexOf("DATE") != -1) {
                type = "VARCHAR(255)";
            }
            
            cs += col + " " + type;
            if (cIt.hasNext()) {
                cs += ",";
            }
        }
        
        cs+=");";
        return cs;
        
        /*
               OID INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        maxThreads INT NOT NULL,
        mailServer VARCHAR(255),
        logLevel VARCHAR(30) NOT NULL,
        logDirectory VARCHAR(255) NOT NULL,
        shutdownThreadWait INT NOT NULL,
        adminMailAddress VARCHAR(255),
        maxMessageSize BIGINT,
        maxMailMessageSize BIGINT,
        PRIMARY KEY (OID),
        UNIQUE UC_OID (OID),
        UNIQUE UC_name (name));
         */              
    }
 
    private static String createInserts(String dataFile) throws Exception{                
        FileInputStream fis = new FileInputStream(new File(dataFile));
        BufferedReader br = new BufferedReader(new InputStreamReader(fis));        
        String line = "";
                
        String cs = "INSERT INTO APSV(";
        while((line = br.readLine()) != null) {
                                
            Iterator cIt = columns.iterator();
            while (cIt.hasNext()) {
                String col = (String) cIt.next();
                cs += col;
                if (cIt.hasNext()) {
                    cs += ",";
                }
            }
        
            cs+=") Values (";
            
            Iterator tIt = types.iterator();
            
            int pos = 0;
            int count = 1;
            while(tIt.hasNext()) {
                String type = (String)tIt.next();
                if (type.indexOf("INT") != -1) {
                    int nextPos = line.indexOf(" ",pos);                   
                    String val = line.substring(pos,nextPos);
                    System.out.println(count + " int val = !" + val + "!");
                    cs += line.substring(pos,nextPos);
                    pos = nextPos + 1;
                }else if (type.indexOf("VARCHAR") != -1) {
                    int nextPos = line.indexOf("\"",pos + 1);                   
                    String val = line.substring(pos+1,nextPos);
                    System.out.println(count + " string val = !" + val + "!");
                    cs += line.substring(pos,nextPos);
                    pos = nextPos + 2;                    
                }else if (type.indexOf("FLOAT") != -1) {
                    int nextPos = line.indexOf(" ",pos);                   
                    String val = line.substring(pos,nextPos);
                    System.out.println(count + " float val = !" + val + "!");
                    cs += line.substring(pos,nextPos);
                    pos = nextPos + 1;
                }else if (type.indexOf("LOGI") != -1) {
                    int nextPos = line.indexOf(" ",pos);                   
                    String val = line.substring(pos,nextPos);
                    System.out.println(count + " logi val = !" + val + "!");
                    cs += line.substring(pos,nextPos);
                    pos = nextPos + 1;
                }else if (type.indexOf("DATE") != -1) {
                    int nextPos = line.indexOf(" ",pos);                   
                    String val = line.substring(pos,nextPos);
                    System.out.println(count + " date val = !" + val + "!");
                    cs += line.substring(pos,nextPos);
                    pos = nextPos + 1;
                }else {
                    System.out.println(count + " TYPE " + type + " NOT FOUND");                    
                }
                count++;
            }
        }                    
//1000 101 "Bill Bomm SR Limited" "123 Disk Drive" "Unit 10" "CPULAND" "NY" "1010101010" "Walton" "3035551212" "9222526" "Bill Bomm SR" "Bill Bomm SR" "3035551515" 3 "3035551414" "" "123-45-6789" "pc1" yes "cc" "net" "sdc" 2 no yes "" 6 1 2 92 1002078 30 04/08/03 "0848" "lidy" 02/27/96 0 -27 17742 349013.39 "" 1408.54 "n" 0 "" 0 "" 38 "" 12799 "" 205 0 1386.04 0 29289 ? 18 ? 150 ? 72.5 no 03/20/03 yes 03/08/02 04/08/03 ? "" 30 0 0 10 10 0 "" 0 0 0 0 0 0 "" 2000 0 0 6240 8000 0 0 0 0 0 0 0 0 0 4 0 ? ? yes no 0 "M" "*" 0 "1-12345" 123456 690.2 1 "" "PARTNERCODEXXXZ" "" "" "R" yes -27512 0 "" "" 0 0 0 0 yes yes yes yes 1 0 0 0 no "" "" "" "" "" 0 "POVERSIONXXZ" "" "" "" 0 no yes "" "" "" "" "" "" "" no no no no no 0 0 0 0 ""        
        
        return cs;
        
        /*
               OID INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        maxThreads INT NOT NULL,
        mailServer VARCHAR(255),
        logLevel VARCHAR(30) NOT NULL,
        logDirectory VARCHAR(255) NOT NULL,
        shutdownThreadWait INT NOT NULL,
        adminMailAddress VARCHAR(255),
        maxMessageSize BIGINT,
        maxMailMessageSize BIGINT,
        PRIMARY KEY (OID),
        UNIQUE UC_OID (OID),
        UNIQUE UC_name (name));
         */              
    }
}